import openpyxl

wb = openpyxl.load_workbook('workbook01.xlsx')
# 获取sheet页
print('获取sheet页')
print(wb.sheetnames)
for sheet in wb:
    print(sheet.title)

# 根据工作表名称获取工作表
print('根据工作表名称获取工作表')
sheet_1 = wb.get_sheet_by_name('1')
print(sheet_1)
print(wb['1'])

# 获取单元格对象及单元格的值
print('获取单元格对象及单元格的值')
ws = wb.active
print(ws)
# 获取单元格的行、列及值
c = ws['B1']
print('Row:{} Column:{} is {}'.format(c.row, c.column, c.value))
print('循环遍历获取'.center(20, '*'))
for r in ws.rows:
    for c in r:
        print(c.value, end='\t')
    print()

# 读取整行、整列及部分几行
ws = wb.active
print('获取整列'.center(20, '*'))
colc = ws['C']  # 获取到 C 列
print(colc)
print('遍历获取整列的数据'.center(20, '*'))
for c in colc:
    print(c.value, end='\t')
print()
print('获取整行数据'.center(20, '*'))
row6 = ws[6]  # 获取第 6 行
print(row6)
print('切片获取部分几行'.center(20, '*'))
row_range = ws[2:4]
print(row_range)
print('循环遍历部分几行数据'.center(20, '*'))
for row in row_range:
    for c in row:
        print(c.value, end='\t')
    print()

# 使用 iter_rows()逐行读取
ws = wb.active
print('共{}行， 共{}列'.format(ws.max_row, ws.max_column))
print('ws.iter_rows()设置最小列、最大列、最小行和最大行读取'.center(20, '*'))
for row in ws.iter_rows(min_row=1, max_row=3, max_col=3, min_col=2):
    for cell in row:
        print(cell.value, end=',')
    print()

# 部分行部分列切片读取
ws = wb.active
print('共{}行， 共{}列'.format(ws.max_row, ws.max_column))
print('部分行部分列切片读取'.center(20, '*'))
cell_range = ws['A1:C3']
for rowObject in cell_range:
    for cellObject in rowObject:
        print(cellObject.coordinate, cellObject.value, end='\t')
    print()
from openpyxl.utils import get_column_letter, column_index_from_string
# 列数字与字母的对应转换
print('列数字与字母的对应转换')
print('第 2 列对应的字母：', get_column_letter(2))
print('第 34 列对应的字母：', get_column_letter(34))
print('第 100 列对应的字母：', get_column_letter(100))
print('AAH 字母列对应的数字：', column_index_from_string('AAH'))
print('B 字母列对应的数字：', column_index_from_string('B'))
